Amazon Redshift DB開発者ガイド – データのロード処理(1).データロードのベストプラクティス

Amazon Redshift DB開発者ガイド

#データベース

#Amazon Redshift

#AWS

しんや

2013.08.19

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

先週末『パシフィック・リム』を3D字幕版で観て、これは吹替版でも新たに観るべきか…と若干迷っている今日この頃、皆様いかがお過ごしでしょうか？しんやです。

先週、RedshiftではAmazon Redshift DB開発者ガイド – テーブル設計のベストプラクティスをエントリとしてアップしましたが、もう1つ『ベストプラクティス』がありましたので同じように切り出してご紹介したいと思います。それがこちらの『データロードのベストプラクティス』です。このエントリ情報を含む『Loading Data』に関しては、その他にも有用な情報が散りばめられていますので、何回かに分けてこちらに展開して行ければと思っております。

はじめに

非常に大規模なデータセットをロードするには、とても長い時間が掛かり、リソースも大量に消費する事になります。どのようにデータをロードするかは、クエリパフォーマンスにもまた影響を及ぼす事に成りかねません。このセクションでは、効果的にCOPYコマンドや、バルクインサート、ステージングテーブルなどを使ってデータロードを行う為のベストプラクティスをご紹介します。

全ての状況に有効となる『銀の弾丸』的なソリューションは存在しません。後のセクションでは、より詳細な説明とテーブル設計オプションの例を紹介します。

データのロードにはCOPYコマンドを使う
復数同時でCOPYコマンドを使用しない
データを復数ファイルに分割する
GZIPを使ってデータファイルを圧縮する
複数行の挿入を使う
バルクインサートを使う
ソートキーの順序でロードする
時系列テーブルを使う
UPSERTを使うためにステージングテーブルを使う
データベースを掃除する
長時間のVacuumsを避けるためにディープコピーを使う
利用可能なメモリを増やす
最新のテーブル統計情報を維持する

データのロードにはCOPYコマンドを使う

Amazon S3やAmazon DynamoDBから並列にデータをロードするためには、COPYコマンドを使います。COPYコマンドはINSERT文を使うよりも遥かに効率的に、大量のデータをロードし、より効果的にデータを格納します。

COPYコマンドを使用する方法の詳細については、Loading data from Amazon S3とLoading data from an Amazon DynamoDB tableをご参照ください。

ソートキーを使用している空のテーブルに対してCOPYコマンドでデータをロードすると、Amazon Redshiftはデータ格納時にソートを行い、クエリのパフォーマンスの向上が見込めます。この点に関する詳細はChoosing the best sort keyをご参照ください。

復数同時でCOPYコマンドを使用しない

復数のファイルから1つのテーブルデータをロードする為に、単一のCOPYコマンドを使用します。Amazon Redshiftは自動的に並列にデータをロードします。

復数のファイルから1つのテーブルにデータをロードする際は、復数で同時にCOPYコマンドを使用しないでください。

復数のCOPYコマンドを使うと、シリアライズ負荷で遥かに遅くなってしまいます。そしてテーブルが定義されたソート列を持っている場合、最後にはVACUUMが必要となってしまいます。並列にデータをロードする為のCOPY利用方法の詳細については、 Loading data from Amazon S3をご参照ください。

データを復数ファイルに分割する

Amazon S3からデータをロードする為にCOPYコマンドを使う時は、単一の大きなファイルから全てのデータをロードする代わりに、最初に復数のファイルに分割しておきましょう。

COPYコマンドは復数のファイルから並列でデータをロードし、クラスタ内のノード間でワークロードを分割します。データをファイルに分割する方法やCOPYコマンドでのデータをロードする使用例については、Loading data from Amazon S3をご参照ください。

GZIPを使ってデータファイルを圧縮する

大規模なデータセットを持っている場合、GZIPを使って個別にロードファイルを圧縮する事を強くお勧めします。

COPYコマンドを使う際は、gzipオプションを指定します。以下例ではパイプ(|)で区切られたGZIPファイルからTIMEテーブルをロードします。

copy time
from 's3://mybucket/data/timerows.gz' 
credentials 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>'
gzip
delimiter '|';

複数行の挿入を使う

もしCOPYコマンドがオプションやSQLでの登録で望んでいるものでは無い場合、可能な限りで複数行の登録(multi-row insert)を使用します。データ圧縮は一度に1行又は少数の行を追加する場合、非効率です。

複数行挿入は、登録の一連の流れをバッチ処理する事により、パフォーマンスを向上させます。以下例では、単一のINSERT文を使用して4列構成のテーブルに3行データを登録しています。

insert into category_stage values
(default, default, default, default),
(20, default, 'Country', default),
(21, 'Concerts', 'Rock', default);

バルクインサートを使う

高パフォーマンスでデータを登録する為には、SELECT文でバルクインサートの操作を行います。

あるテーブルから別のテーブルへデータやサブセットを移動させる必要がある場合、INSERT文とCREATE TABLE ASコマンドを使用します。例えば、以下のINSERT文では、CATEGORYテーブルから全ての行を照会し、結果をCATEGORY_STAGEテーブルへ登録しています。

insert into category_stage
(select * from category);

以下の例では、CREATE文でCATEGORYテーブルのコピーとしてCATEGORY_STAGEテーブルを作成し、CATEGORYテーブルの参照結果全てをCATEGORY_STAGEテーブルに登録しています。

create table category_stage as
select * from category;

ソートキーの順序でロードする

vacuum実施を避ける為に、ソートキーの順序でデータをロードするようにしましょう。

データは適切にソート順に格納されるので、VACUUMは必要無いかも知れません。例えば、現在の一日の活動に基いて毎日データをロードするとします。もしソートキーがタイムスタンプ列に指定されていれば、あなたのデータは効果的に格納されて行くでしょう。何故なら、当日のデータは必ず前日のデータの最後に追加されていくからです。

時系列テーブルを使う

もし対象のデータに一定の保存期間があり、テーブルとしては同じだけど異なる時間範囲のデータを含んでいる場合、我々は時系列テーブルのシーケンスとしてデータを整理する事を強くお勧めします。対応するテーブルにDROP TABLE文を実行する事により、簡単に古いデータを削除する事が出来ます。これは、大規模なDELETE文を実行するよりも遥かに高速であり、またスペースを再利用するのでその後VACUUMを実行する必要が無くなります。

データが別のテーブルに格納されていると言う事実を隠す為に、UNION ALLのビューを作成する事が出来ます。古いデータを削除する時、ドロップされたテーブルを削除する為に単にUNION ALLビューを絞り込みます。

UPSERTを使うためにステージングテーブルを使う

最初にステージングテーブルにデータをロードしておく事によって、効率的に更新を行い、新しいデータを登録する事が出来ます。

Amazon RedshiftがUPSERT(更新 or 登録を一度に実行：1つのデータソースから登録または更新を行う)コマンドををサポートしていない一方で、ステージングテーブルにデータをロードし、UPDATE文及びINSERT文のターゲットテーブルとステージングテーブルを結合しておく事で、あなたは効果的にUPSERT操作を行う事が出来ます。

手順については、Updating and inserting new dataをご参照ください。

データベースを掃除する

ソートキーの順序でデータをロードしない限り、追加・削除、または多数の変更を行う度にVACUUMコマンドが実行されます。VACUUMコマンドは、ソート順序を維持し、性能を復元する為に、データを再編成します。

ソートキーを使っているテーブルに対してロードを行う為にCOPYコマンドを実行する時は、Amazon Redshiftはディスクに格納する前にデータをソートします。(詳細については、Loading data in sort key orderをご参照ください。) テーブルに大量の新規データ追加を行う場合、新しい行が既存の行に対してソートされていないのでパフォーマンスが低下する可能性があります。

テーブルのVACUUM操作に関する詳細については、Vacuuming tablesをご参照ください。

長時間のVacuumsを避けるためにディープコピーを使う

ディープコピーはバルクインサート使う事で、再生成と再移入を行います。もしテーブルが広い範囲でソートされていない領域を持つ場合、ディープコピーはVACUUMよりも遥かに高速に働きます。トレードオフは、VACUUM中には行えていた同時更新が、ディープコピー操作中には行えない、という事です。

ディープコピーを実行するには、以下の手順を実行します。

1.現在のテーブルと同じ属性を持つ新しいテーブルを作成します。
- 新しいテーブルを作成するために、現在のテーブルのCREATE TABLE DDL文をコピーして使ってください。
- 或いは、新しいテーブルを作成する文のようにCREATE TABLE … LIKEを使う事も出来ますが、その場合新しいテーブルは主キーと外部キー制約が既に存在する場合、それらを継承しません。
2.現在のテーブルから新しいテーブルへ行をコピーする為に、INSERT INTO … SELECT文を実行します。
3.現在のテーブルをDROPします。
4.新しいテーブル名を元のテーブル名に変更する為にALTER TABLE文を実行します。

以下の例では、INSERT INTO … SELECT文と同じ様にCREATE TABLE … LIKEを使い、SALESテーブルに対してディープコピーを実行しています。もしオリジナルのCREATE TABLE DDL文が利用出来る場合、最初の実行文をスキップする代わりに利用します。

create table likesales (like sales);
insert into likesales (select * from sales);
drop table sales;
alter table likesales rename to sales;

もしCREATE TABLE … LIKEを使う場合、新しいテーブルは元のテーブルの主キーと外部キー属性を継承しません。もしオリジナルのCREATE TABLE DDLが利用出来ない場合、主キーと外部キー属性を保持する必要があります。DROP TABLEの代わりにTRUNCATEを使う事で、これらの属性を保持する事が出来ます。

欠点としては、2つのインサート文を使用する為、操作に時間が掛かるという事です。

create temp table tempsales as select * from sales;
truncate sales;
insert into sales (select * from tempsales);
drop table tempsales;

利用可能なメモリを増やす

もしデータのロードやVACUUMに時間が掛かるようであれば、wlm_query_slot_countを増加させて、COPYやVACUUMコマンドに対する利用可能なメモリを増やしましょう。

データのロードとVACUUMはメモリを集中的に扱う事が出来ます。追加メモリをCOPY文やVACUUM文に対して割り当てると、操作の間wlm_query_slot_countパラメータの値が増加する事によって、パフォーマンスが向上します。但し、wlm_query_slot_countの値の増加は、クラスタ上で実行出来る他の同時実行クエリの数を減らさざるを得なくなります。パラメータと同時実行の影響についての詳細はwlm_query_slot_countをご参照ください。